


ALTER     function fn_load_all_kitpart_numbers (@mktg_part_number varchar(50))
returns  @fn_results Table (Part_Number varchar(50))
as 
begin
	declare @p_type varchar(50)
	declare @product_id int
	declare @part_number varchar(50)
	
	declare @results Table (Part_Number varchar(50))
	select @p_type = P_Type,@product_id = Product_ID from Product where Part_Number = @mktg_part_number
	
	if(@p_type = 'KIT')
	begin

		declare kitCursor CURSOR FOR
			select Part_Number 
			FROM Kit_Table, Product WHERE Kit_Table.Product_ID = @product_id and
			Kit_Table.ProcessStep_ID = Product.ProcessStep_ID
	
		open kitCursor 
		fetch next from kitCursor into @part_number
		while @@fetch_status = 0
			begin			
				insert into @results (Part_Number) values (@part_number)
				fetch next from kitCursor into  @part_number
			end

		close kitCursor
		deallocate kitCursor		
	end
	else
	begin
		insert into @results (Part_Number) values (@mktg_part_number)		
	end

	insert @fn_results select *from @results
	return
end




